package com.jc.test;

import java.io.BufferedReader;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.jc.test.vo.JuHeApiVo;
import com.jc.test.vo.LogisticsVo;



/**
 * 
 * 0351602ff65df76ac78150f5ddfcc346
 * http://v.juhe.cn/exp/
 * 常用快递调用示例代码 － 聚合数据 在线接口文档：http://www.juhe.cn/docs/43
 **/

public class JuheApi2 {
	
	private static final Logger logger = Logger.getLogger(JuheApi2.class);
			
	public static final String DEF_CHATSET = "UTF-8";
	public static final int DEF_CONN_TIMEOUT = 30000;
	public static final int DEF_READ_TIMEOUT = 30000;
	public static String userAgent = "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.66 Safari/537.36";
	public static String juheKey = "0351602ff65df76ac78150f5ddfcc346";
	public static String baseUrl = "http://v.juhe.cn/exp/";
	// 配置您申请的KEY
	//public static final String APPKEY = "7bcb8470d9a6830faf61e622caff39a1";

	/**
	 * 常用快递查询API
	 * 
	 * @param com
	 *            快递公司编号
	 * @param no
	 *            快递号
	 * @return
	 */
	public static JuHeApiVo getExp(String com, String no) {
		String result = null;
		String url = baseUrl +"index";// 请求接口地址
		Map<String, Object> params = new HashMap<String, Object>();// 请求参数
		params.put("com", com);// 需要查询的快递公司编号
		params.put("no", no);// 需要查询的订单号
		params.put("key", juheKey);// 应用APPKEY(应用详细页查询)
		params.put("dtype", "");// 返回数据的格式,xml或json，默认json
		JuHeApiVo juhe = new JuHeApiVo();
		try {
			result = net(url, params, "POST");
			JSONObject object = JSON.parseObject(result);
		
			if (object.getIntValue("error_code") == 0) {
				juhe = JSONObject.parseObject(object.get("result").toString(), JuHeApiVo.class);
			}
			if(object.getIntValue("error_code") > 0) {
				//throw new Exception("查询快递错误:" + object.get("reason").toString());
			}
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		}
		return juhe;
	}

	/**
	 * 快递公司编号对照表
	 * 
	 * @return
	 */
	public static String getCourierNo() {
		String result = null;
		String url = baseUrl +"com";// 请求接口地址
		Map<String, Object> params = new HashMap<String, Object>();// 请求参数
		params.put("key", juheKey);// 应用APPKEY(应用详细页查询)
		try {
			result = net(url, params, "POST");
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		}
		return result;
	}

	/**
	 *
	 * @param strUrl
	 *            请求地址
	 * @param params
	 *            请求参数
	 * @param method
	 *            请求方法
	 * @return 网络请求字符串
	 * @throws Exception
	 */
	public static String net(String strUrl, Map<String, Object> params, String method) throws Exception {
		HttpURLConnection conn = null;
		BufferedReader reader = null;
		String rs = null;
		try {
			StringBuffer sb = new StringBuffer();
			if (method == null || method.equals("GET")) {
				strUrl = strUrl + "?" + urlencode(params);
			}
			URL url = new URL(strUrl);
			conn = (HttpURLConnection) url.openConnection();
			if (method == null || method.equals("GET")) {
				conn.setRequestMethod("GET");
			} else {
				conn.setRequestMethod("POST");
				conn.setDoOutput(true);
			}
			conn.setRequestProperty("User-agent", userAgent);
			conn.setUseCaches(false);
			conn.setConnectTimeout(DEF_CONN_TIMEOUT);
			conn.setReadTimeout(DEF_READ_TIMEOUT);
			conn.setInstanceFollowRedirects(false);
			conn.connect();
			if (params != null && method.equals("POST")) {
				try {
					DataOutputStream out = new DataOutputStream(conn.getOutputStream());
					out.writeBytes(urlencode(params));
				} catch (Exception e) {
					// TODO: handle exception
				}
			}
			InputStream is = conn.getInputStream();
			reader = new BufferedReader(new InputStreamReader(is, DEF_CHATSET));
			String strRead = null;
			while ((strRead = reader.readLine()) != null) {
				sb.append(strRead);
			}
			rs = sb.toString();
		} catch (IOException e) {
			logger.error(e.getMessage(),e);
		} finally {
			if (reader != null) {
				reader.close();
			}
			if (conn != null) {
				conn.disconnect();
			}
		}
		return rs;
	}

	// 将map型转为请求参数型
	private static String urlencode(Map<String, Object> data) {
		StringBuilder sb = new StringBuilder();
		for (Map.Entry<String, Object> i : data.entrySet()) {
			try {
				sb.append(i.getKey()).append("=").append(URLEncoder.encode(i.getValue() + "", "UTF-8")).append("&");
			} catch (UnsupportedEncodingException e) {
				logger.error(e.getMessage(),e);
			}
		}
		return sb.toString();
	}
	
	
	
	public static void testXlsx() throws Exception {
	    File file = new File("H:\\0111\\333\\111111.xlsx");
//	    System.out.println(file.exists());
	    //获取输入流  
	    InputStream stream = new FileInputStream(file); 
	    Workbook xssfWorkbook = new XSSFWorkbook(stream); 
	    Sheet sheet = xssfWorkbook.getSheetAt(2); 
	    
	    //获得当前sheet的开始行
        int firstRowNum  = sheet.getFirstRowNum();
        //获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();
        //循环除了第一行的所有行
        int count = 0;
        //为了过滤到第一行因为我的第一行是数据库的列
        for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
        	//获得当前行
        	Row row = sheet.getRow(rowNum);
        	if(row == null) {
        		continue;
        	}
        	Cell cell2 = row.getCell(2);
        	Cell cell3 = row.getCell(16);
        	Cell cell4 = row.getCell(17);
        	String orderid = null;
        	if(cell2 != null) {
        		orderid = String.valueOf(new Double(cell2.getNumericCellValue()).intValue());
        		//orderid = cell2.getStringCellValue();
        		if(StringUtils.isNotBlank(orderid)) {
//        			orderid = orderid.trim();
        			orderid = StringUtils.trim(orderid);
        		}
        	}
        	
        	//System.out.println("cell3");
        	String expname = null;
        	if(cell3 != null) {
        		expname = cell3.getStringCellValue();
        	}
        	
        	//System.out.println("cell4");
        	String expNo = null;
        	if(cell4 != null) {
        		expNo = String.valueOf(cell4.getStringCellValue());
        	}

        	if(StringUtils.isNotBlank(orderid) && StringUtils.isNotBlank(expname) && StringUtils.isNotBlank(expNo)) {
        		count++;
//        		System.out.println(count + " :" + orderid + "----------------" + expname + "----------- " + expNo + "-----  "  );
        		
        		
        		String stat = "";
        		try {
        			
        			JuHeApiVo vo = getExp(expname,expNo);
            		List<LogisticsVo> list = vo.getList();
            		
            		for(LogisticsVo v : list) {
            			String momo = v.getRemark();
            			String sj = v.getDatetime();
            			stat = "未签收";
            			if(StringUtils.isNotBlank(momo)) {
            				if(momo.indexOf("已收取") > 0 || momo.indexOf("已签收") > 0 || momo.indexOf("签收") > 0 || momo.indexOf("代收") > 0 || momo.indexOf("代签收") > 0) {
            					stat = sj + " : " + momo;
            					continue;
            				}
            			}
            		}
            		
        		}catch (Exception e) {
					stat = "查不到信息，请检查物流单号";
				}
        		
        		Cell cv = row.getCell(17);
        		if(cv != null) {
        			cv.setCellValue(stat);
        		}
        		
        		System.out.println(orderid + "|" + stat);
        		
        		
        	}
        	

//            FileOutputStream out= new java.io.FileOutputStream("H:\\0111\\333\\1111112.xlsx");
//            xssfWorkbook.write(out);
//            out.close();
        }
        
        
        

        
        

	}
	
	
	public static void main(String[] args) {
		
		
		try {
			testXlsx();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
//		String str = getCourierNo();
//		System.out.println(str);
//		JSONObject retExp = JSONObject.parseObject(str);
//		JSONArray arr = retExp.getJSONArray("result");
//		for(int i = 0 ; i < arr.size() ; i++) {
//			JSONObject ex = arr.getJSONObject(i);
//			System.out.println("物流公司: " + ex.getString("com") + "   代码: " + ex.getString("no") + "");
//		}
//		
		
		
		
//		JuHeApiVo vo = getExp("sf","074096664331");
//		List<LogisticsVo> list = vo.getList();
//		String stat = "";
//		for(LogisticsVo v : list) {
//			String momo = v.getRemark();
//			String sj = v.getDatetime();
//			stat = "未签收";
//			if(StringUtils.isNotBlank(momo)) {
//				if(momo.indexOf("已收取") > 0 || momo.indexOf("已签收") > 0 || momo.indexOf("签收") > 0) {
//					stat = sj + " : " + momo;
//					continue;
//				}
//			}
//		}
//		
		
		//String str2 = JSONObject.toJSONString(vo);
		//System.out.println(stat);
	}
}